﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using MyBasicLib.Reflection;
using MyBasicLib.Linq.Expressions;
using MyBasicLib.Extensions;
using MyBasicLib.Utilities;
using System.Data;
using MyBasicLib.Data.ORMLiteV2;

namespace MyBasicLib.Data.ORMLiteNew.SqlClient
{

    class CmdCache<T>
    {
        public static string Insertstring;
        public static string Updatestring;

    }
    public class DbQueryProvider : ORMLiteNew.DbQueryProvider
    {
        public DbQueryProvider()
        {
            this.Executor = IOC.DefaultContainer.Resolve<IDatabase>(); ;
        }
        string NOLOCK = string.Empty;//" WITH(NOLOCK) ";
        //static IMappingProvider mappingProvider = new AttributeMappingProvider();
        const string ParameterToken = "@";

        /// <summary>
        /// 处理新增
        /// </summary>
        /// <param name="iInfo"></param>
        /// <returns></returns>
        System.Data.IDbCommand CreateCommand<T>(InsertDbQueryableInfo<T> iInfo)
        { 
            if (iInfo.Entity != null)
            {
                var entity = iInfo.Entity;
                var type = entity.GetType(); 
                System.Diagnostics.Debug.Assert(type == typeof(T));
                var tdata = TypeDataCache.GetTypeData<T>();
                var properties = tdata.GetPropertyInfos().Where(p =>
                {
                    var ca = TableNameAliasCache.MappingProvider.GetColumnAttribute(p.Value);
                    return ca != null && !ca.IsDbGenerated;
                }).ToArray();
                int idx = 0;
                var parametes = properties.Select(
                    k => CreateParameter(ParameterToken, idx++, k.Value.FastGetValue(entity))).ToArray();
                var parameteNames = parametes.Select(p => p.ParameterName);
                if (CmdCache<T>.Insertstring == null)
                {
                    string[] propertyNames = properties.
                        Select(p => string.Format(CultureInfo.InvariantCulture, "[{0}]", p.Key)).ToArray();
                    string columnString = string.Join(",", propertyNames);
                    string valueString = string.Join(",", parameteNames);

                    string sql = string.Format(CultureInfo.InvariantCulture,
                                       "INSERT INTO [{0}] ({1}) VALUES({2})",
                                       TableNameAliasCache.MappingProvider.GetTableAttribute(type).TableName,
                                       columnString, valueString);
                    CmdCache<T>.Insertstring = sql;
                }

                var cmd = Executor.CreateCommand(CmdCache<T>.Insertstring);

                for (var i = 0; i < parametes.Length; i++)
                {
                    cmd.Parameters.Add(parametes[i]);
                }
                return cmd;
            }
            else
            {
                var sb = new StringBuilder();
                sb.AppendFormat("INSERT INTO [{0}] ", TableNameAliasCache.MappingProvider.GetTableAttribute(iInfo.SelectDbQueryableInfo.ResultType).TableName);//.ResultType

                sb.Append("(");
                var idx = sb.Length;//记录当前位置
                sb.Append(") ");
                List<SelectClause> selectClause;
                List<object> parameterValues = CreateSelectCommandHelper(iInfo.SelectDbQueryableInfo, sb, out selectClause);
                string str = string.Join(",", selectClause.Select(p => p.AliName));
                sb.Insert(idx, str);// 
                var cmd = Executor.CreateCommand(sb.ToString());
                for (var i = 0; i < parameterValues.Count; i++)
                {
                    cmd.Parameters.Add(CreateParameter(ParameterToken, i, parameterValues[i]));
                }
                return cmd;
            }
        }
        /// <summary>
        /// 处理查询
        /// </summary>
        /// <param name="sInfo"></param>
        /// <returns></returns>
        IDbCommand CreateCommand<T>(SelectDbQueryableInfo<T> sInfo)
        {
            StringBuilder sb = new StringBuilder();
            List<SelectClause> selectClause;
            List<object> parameterValues = CreateSelectCommandHelper(sInfo, sb, out selectClause);
            selectClause = null;
            var cmd = Executor.CreateCommand(sb.ToString());
            for (var i = 0; i < parameterValues.Count; i++)
            {
                cmd.Parameters.Add(CreateParameter(ParameterToken, i, parameterValues[i]));
            }
            return cmd;
        }

        private List<object> CreateSelectCommandHelper<T>(SelectDbQueryableInfo<T> sInfo, StringBuilder sb, out List<SelectClause> selectClause)
        {
            TableNameAliasCache tCache = new TableNameAliasCache();
           
            foreach (var j in sInfo.JoinExpressions)
            {
                tCache.GetTableAlias(j.Expressions[1]);
                tCache.GetTableAlias(j.Expressions[2]);
            }
            var selectParser = new SelectTranslator();
            var whereParser = new WhereTranslator();
            bool isCount = sInfo.IsCount;
            sb.Append("SELECT ");
            if (!isCount && sInfo.IsDistinct)
            {
                sb.Append("DISTINCT ");
            }
            if (!isCount && sInfo.Take > 0 && sInfo.Skip == 0)//>1
            {
                sb.Append("TOP (");
                selectParser.HandleParameter(sb, sInfo.Take);
                sb.Append(") ");
            }
            int idx = sb.Length;
            selectParser.Translate(sInfo.Expression, sb, selectParser.ParameterValues.Count, tCache, TableNameAliasCache.MappingProvider);
            sb.Length = idx;
            if (isCount)
            {
                if (sInfo.IsDistinct)
                {
                    if (selectParser.SelectClauses.Count > 0)
                    {
                        sb.Append("COUNT(DISTINCT ");
                        sb.Append(selectParser.SelectClauses[0].SourceName);
                        sb.Append(")");
                    }

                }
                else
                {
                    sb.Append("COUNT(*)");
                }
            }
            else
            {

                string sl = string.Join(",",
                                        selectParser.SelectClauses.Select(p => p.SourceName + " AS " + p.AliName).
                                            ToArray());
                sb.Append(sl);
            }
            whereParser.ParameterValues.AddRange(selectParser.ParameterValues);
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] [t0]", TableNameAliasCache.MappingProvider.GetTableAttribute(sInfo.SourceType).TableName);
            if (!string.IsNullOrEmpty(NOLOCK))
                sb.Append(NOLOCK);
            AppendJoin(sb, sInfo.JoinExpressions, tCache);
            AppendWhere(sb, whereParser, sInfo.WhereExpression, tCache);
            if (!isCount)
            {
                if (sInfo.Skip > 0)
                {
                    string selectCols = string.Join(",", selectParser.SelectClauses.Select(p => p.AliName).ToArray());
                    string old = sb.ToString();
                    sb.Length = 0;
                    sb.Append("WITH cet0([ROW_NUMBER]," + selectCols + ") AS (");
                    sb.Append(" SELECT ROW_NUMBER() OVER (");
                    AppendOrderBy(sb, sInfo.OrderExpressions, tCache, false);
                    sb.Append(") AS [ROW_NUMBER],");
                    sb.Append(selectCols);
                    sb.Append(" FROM (");
                    sb.Append(old);
                    sb.Append(") AS [tmpT1]");
                    sb.Append(")");

                    sb.AppendFormat("SELECT {0} FROM cet0 [tc0] ", selectCols);
                    sb.AppendFormat("WHERE [tc0].[ROW_NUMBER] BETWEEN @p{0} + 1 AND @p{0} + @p{1}",
                                    whereParser.ParameterValues.Count, whereParser.ParameterValues.Count + 1);
                    sb.Append(" ORDER BY [tc0].[ROW_NUMBER]");
                    whereParser.ParameterValues.Add(sInfo.Skip);
                    whereParser.ParameterValues.Add(sInfo.Take);
                }
                else AppendOrderBy(sb, sInfo.OrderExpressions, tCache);
            }
            selectClause = selectParser.SelectClauses;
            return whereParser.ParameterValues;
        }


        internal protected override System.Data.IDbCommand CreateCommand<T>(IDbQueryableInfo<T> dbQueryableInfo)
        {
            var iInfo = dbQueryableInfo as InsertDbQueryableInfo<T>;
            if (iInfo != null)
            {
                return CreateCommand(iInfo);
            }
            var uInfo = dbQueryableInfo as UpdateDbQueryableInfo<T>;
            if (uInfo != null)
            {

                return CreateCommand(uInfo);
            }
            var dInfo = dbQueryableInfo as DeleteDbQueryableInfo<T>;
            if (dInfo != null)
            {
                return CreateCommand(dInfo);
            }
            var sInfo = dbQueryableInfo as SelectDbQueryableInfo<T>;
            if (sInfo != null)
            {
                return CreateCommand(sInfo);
            }
            throw new NotImplementedException();
        }
        private IDbCommand CreateCommand<T>(DeleteDbQueryableInfo<T> uInfo)
        {
            Type sourceType = uInfo.SelectDbQueryableInfo.SourceType;
            var sb = new StringBuilder();
            var whereParser = new WhereTranslator();
            TableNameAliasCache tCache = new TableNameAliasCache();
            sb.Append("DELETE [t0]");
            sb.Append(" FROM ");
            sb.AppendFormat("[{0}] AS [t0]", TableNameAliasCache.MappingProvider.GetTableAttribute(sourceType).TableName);
            AppendJoin(sb, uInfo.SelectDbQueryableInfo.JoinExpressions, tCache);
            AppendWhere(sb, whereParser, uInfo.SelectDbQueryableInfo.WhereExpression, tCache);

            string sqlwhere = sb.ToString();
            var parameterValues = whereParser.ParameterValues;
            var cmd = Executor.CreateCommand(sb.ToString());
            for (var i = 0; i < parameterValues.Count; i++)
            {
                cmd.Parameters.Add(CreateParameter(ParameterToken, i, parameterValues[i]));
            }
            return cmd;
        }
        private IDbCommand CreateCommand<T>(UpdateDbQueryableInfo<T> uInfo)
        {
            if (uInfo.Entity != null)
            {
                var entity = uInfo.Entity;
                var type = entity.GetType();
                var tdata = TypeDataCache.GetTypeData<T>();
                var keys = TableNameAliasCache.MappingProvider.GetTableAttribute(type).KeyName.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var ps = tdata.GetPropertyInfos().Where(p => !keys.Contains(p.Key)).OrderBy(p => p.Key).ToArray();
                //.Where(p=>!p.IsDefined(typeof(DbGeneratedAttribute),false)).ToArray();
                var keyps = tdata.GetPropertyInfos().Where(p => keys.Contains(p.Key)).OrderBy(p => p.Key).ToArray();
                var parameterValues = new List<object>(); ;

                foreach (var info in ps)
                {
                    parameterValues.Add(info.Value.FastGetValue(entity));
                } foreach (var info in keyps)
                {
                    parameterValues.Add(info.Value.FastGetValue(entity));
                }
                if (CmdCache<T>.Updatestring == null)
                {
                    var parameterCount = 0;
                    var sb = new StringBuilder();
                    sb.Append("UPDATE [t0] SET ");
                    if (ps.Length == 0) return null;
                    if (keyps.Length == 0) return null;
                    foreach (var info in ps)
                    {
                        sb.AppendFormat(CultureInfo.InvariantCulture, "[t0].[{0}] = ", info.Key);
                        sb.Append("@p");
                        sb.Append(parameterCount++);
                        sb.Append(",");
                    }
                    --sb.Length;
                    sb.Append(" FROM ");
                    sb.AppendFormat("[{0}] AS [t0]", TableNameAliasCache.MappingProvider.GetTableAttribute(type).TableName);
                    sb.Append(" WHERE ");
                    foreach (var key in keyps)
                    {
                        sb.AppendFormat(CultureInfo.InvariantCulture, "[t0].[{0}] = ", key.Key);
                        //object keyValue = key.FastGetValue(newValue); 
                        sb.Append("@p");
                        sb.Append(parameterCount++);
                        sb.Append(" AND ");
                    }
                    sb.Length -= 5; //删除' AND '  
                    CmdCache<T>.Updatestring = sb.ToString();
                }
                var parametes = parameterValues;
                var cmd = Executor.CreateCommand(CmdCache<T>.Updatestring);
                for (var i = 0; i < parametes.Count; i++)
                {
                    cmd.Parameters.Add(CreateParameter(ParameterToken, i, parametes[i]));
                }
                return cmd;
            }
            else
            {
                var sb = new StringBuilder();
                var whereParser = new WhereTranslator();
                TableNameAliasCache tCache = new TableNameAliasCache();
                sb.Append("UPDATE [t0] SET ");
                var updateActionParser = new UpdateActionTranslator();
                int idx = sb.Length;
                updateActionParser.Translate(uInfo.Expression, sb, 0, tCache, TableNameAliasCache.MappingProvider);
                if (sb.Length > idx) //删除','
                {
                    sb.Length--;
                }
                whereParser.ParameterValues.AddRange(updateActionParser.ParameterValues);
                sb.Append(" FROM ");
                sb.AppendFormat("[{0}] AS [t0]", TableNameAliasCache.MappingProvider.GetTableAttribute(uInfo.SelectDbQueryableInfo.SourceType).TableName);
                AppendJoin(sb, uInfo.SelectDbQueryableInfo.JoinExpressions, tCache);
                AppendWhere(sb, whereParser, uInfo.SelectDbQueryableInfo.WhereExpression, tCache);
                var parameterValues = whereParser.ParameterValues;
                var cmd = Executor.CreateCommand(sb.ToString());
                for (var i = 0; i < parameterValues.Count; i++)
                {
                    cmd.Parameters.Add(CreateParameter(ParameterToken, i, parameterValues[i]));
                }
                return cmd;
            }
        }
        #region Helper

        public static string GetMemberName(Expression expression, TableNameAliasCache tCache, bool appendPre = true)
        {

            Expression nExp = expression;              //简化
            var lExp = nExp as LambdaExpression; //
            if (lExp != null)                   //
                nExp = lExp.Body;                //
            var name = string.Empty;           //
            nExp = nExp.TrimUnary();
            MemberExpression mp = nExp as MemberExpression;
            Expression newExpression = mp.Expression.TrimUnary();
            if (TableNameAliasCache.MappingProvider.IsValidTableClass(newExpression.Type))
            {
                name = mp.Member.Name;
            }

            if (appendPre)
            {
                name = string.Format("[{0}].[{1}]", tCache.GetTableAlias(expression), name);
            }
            else
            {
                name = string.Format("[{0}]", name);
            }
            return name;
        }


        private string AppendWhere(StringBuilder sb, WhereTranslator whereParser, Expression expression,
                                          TableNameAliasCache tablecache)
        {
            int sidx = sb.Length;
            if (expression != null)
            {
                sb.Append(" WHERE ");
                whereParser.Translate(expression, sb, whereParser.ParameterValues.Count, tablecache, TableNameAliasCache.MappingProvider);
            }

            return sb.ToString(sidx, sb.Length - sidx);
        }
        private static string AppendOrderBy(StringBuilder sb, Queue<DbExpression> qOrder, TableNameAliasCache tablecache,
                                             bool appendPre = true)
        {
            int sidx = sb.Length;
            if (qOrder.Count > 0)
            {
                DbExpression exp;
                sb.Append(" ORDER BY ");
                while (qOrder.Count > 0)
                {
                    exp = qOrder.Dequeue();
                    var cons = exp.Expression as ConstantExpression;
                    sb.Append(cons == null ? GetMemberName(exp.Expression, tablecache, appendPre) : cons.Value);
                    if (exp.DbExpressionType == DbExpressionType.OrderByDescending ||
                        exp.DbExpressionType == DbExpressionType.ThenByDescending)
                    {
                        sb.Append(" DESC");
                    }
                    sb.Append(",");
                }
                sb.Length--;

            }
            return sb.ToString(sidx, sb.Length - sidx);
        }
        private static string GetJoinTypeString(JoinType joinType)
        {
            switch (joinType)
            {
                case JoinType.InnerJoin:
                    return "INNER JOIN";
                case JoinType.LeftJoin:
                    return "LEFT JOIN";
                case JoinType.CrossJoin:
                    return "CROSS JOIN";
                default:
                    break;
            }
            return string.Empty;
        }
        private string AppendJoin(StringBuilder sb, Queue<DbExpression> qJoin, TableNameAliasCache tablecache)
        {
            int sidx = sb.Length;
            if (qJoin.Count > 0)
            {
                while (qJoin.Count > 0)
                {
                    var exp = qJoin.Dequeue();
                    var joinType = JoinType.InnerJoin;
                    if (exp.DbExpressionType == DbExpressionType.GroupJoin)
                    {
                        joinType = JoinType.LeftJoin;
                    }
                    sb.Append(" " + GetJoinTypeString(joinType));
                    //if (joinType == JoinType.CrossJoin)
                    //{
                    //    Type intype = (exp.Expressions[0].Eval()).GetType().GetGenericArguments()[0]; 

                    //    sb.AppendFormat(" [{0}] [{1}] ", mappingProvider.GetTableAttribute(intype).TableName,
                    //                    tablecache.GetTableAlias(exp.Expressions[2]));
                    //}
                    //else
                    {
                        tablecache.GetTableAlias(exp.Expressions[1]);
                        Type intype = (exp.Expressions[0].Eval()).GetType().GetGenericArguments()[0];
                        sb.AppendFormat(" [{0}] [{1}] ", TableNameAliasCache.MappingProvider.GetTableAttribute(intype).TableName,
                                        tablecache.GetTableAlias(exp.Expressions[2]));
                        if (!string.IsNullOrEmpty(NOLOCK))
                        {
                            sb.Append(NOLOCK); sb.Append(" ");
                        }
                        sb.Append("ON ");

                        sb.Append(GetMemberName(exp.Expressions[1], tablecache));

                        sb.Append("=");
                        sb.Append(GetMemberName(exp.Expressions[2], tablecache));

                    }
                    sb.Append(" ");
                }
                sb.Length--;
            }
            return sb.ToString(sidx, sb.Length - sidx);
        }

        #endregion

        public override IDatabase Executor
        {
            get;
            protected set;
        }
    }
}